using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using DynamiCubeLib;
using System.Data.OleDb;
using System.IO;

public partial class QCAnalytic : System.Web.UI.Page
{
    String basepath = "";
    string connString = "";
    ArrayList arrList = null;
    DynamiCubeLib.DCubeClass axDCube2 = new DynamiCubeLib.DCubeClass();
    //DynamiCubeLib.DCube axDCube2 = new DCube();
    ArrayList cols = new ArrayList();
    DataSet dds = null;

    String database = @"QCAnalytic\services.mdb";

    public String getRequest(String item, String pref)
    {
        foreach (String str in Request.Form.Keys)
        {
            if (str == pref + item)
            {
                return Request[str];
            }
        }
        return null;
    }
    public String getRequest(String item)
    {
        String pref = "ctl00$ContentPlaceHolder3$";
        return getRequest(item, pref);
    }

    public Boolean isKeyExistInSession(String key)
    {
        foreach (Object objky in Session.Keys)
        {
            String ky = (String)objky;
            if (ky == key)
            {
                return true;
            }
        }
        return false;
    }

    public Boolean isKeyExistInHash(Hashtable hsh, String key)
    {
        foreach (Object obj in hsh.Keys)
        {
            if (key == (String)obj)
            {
                return true;
            }
        }
        return false;
    }

    private Boolean isEditMode()
    {
        if (Request.QueryString["PK"] != null)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        //Object obj = Request;
        //Boolean isTableExist=false;

        /*foreach (Object objky in Session.Keys)
        {
            String ky = (String)objky;
            if (ky == "tbl")
            {
                isTableExist=true;
                break;
            }
        }*/

        //ArrayList arrList = getColumnFromSql("sy_analytic_fields", "analytic_type", "analytic_id","55");

        security hdds = new security();
        if (Session["userid"] == null) Response.Redirect(hdds.SessionExpiryURL);
        if (Session["userid"].ToString() == "") Response.Redirect(hdds.SessionExpiryURL);

        //Code added by Sampath on 18-Nov-2011 for IPP Vertical privilege
        if (Request.QueryString["mn"] != null)
        {
            if (!hdds.checkObjectMenuAccess(Request.QueryString["mn"]))
            {
                Panel1.Visible = false;
                Panel2.Visible = false;
                Panel3.Visible = false;
                LblMessage.Text = wrapItAsMessage("<img src=\"Images/info.gif\" alt=\"\"></img>&nbsp;You do not have sufficient access permission.", true);
                return;
            }
        }

        if (!IsPostBack)
        {
            Panel1.Visible = true;
            Panel2.Visible = false;
            Panel3.Visible = false;
            basepath = System.AppDomain.CurrentDomain.BaseDirectory;
            DataTable dt2 = null;
            HDDatabase hds = new HDDatabase();
            dt2 = hds.GetDataTable("select * from sy_report_sql", "qdt");
            hds = null;
            ArrayList qal = getColumnFromDT(dt2, 1);
            //FillCombo(ListQuery, qal);
            FillCombo(ListQuery, dt2, 2, 0);
            /*hds = new HDDatabase();
            DataTable dtt= hds.GetDataTable("select * from ap_emp");
            FillCombo(CmbSavedData,)*/

            if (Request.QueryString["PK"] != null)
            {
                //processQuery(Request.QueryString["PK"]); //balaji 28/01/2008
                DataTable dt = getAnalyticFields("sy_analytic", "af_row_id", Request.QueryString["PK"]);

                Object data0 = dt.Rows[0]["af_row_id"];
                Object data1 = dt.Rows[0]["analytic_id"];
                Object data2 = dt.Rows[0]["analytic_name"];
                Object data3 = dt.Rows[0]["analytic_desc"];
                Object data4 = dt.Rows[0]["analytic_query"];
                TxtAnalyticsID.Text = (String)data1;
                TxtAnalyticsDesc.Text = (String)data3;
                TxtAnalytics.Text = (String)data2;
                ListQuery.SelectedValue = (String)data4;
            }
            PnlTable.Controls.Clear();
            Session["tbl"] = null;
        }



        Table tbl = null;
        if (isKeyExistInSession("tbl"))
        {
            tbl = (Table)Session["tbl"];
            if (tbl != null)
            {
                tbl = (Table)Session["tbl"];
                String data = null;
                foreach (TableRow tr in tbl.Rows)
                {
                    //data = getRequest(tr.Cells[0].Text);
                    Button tcbut = new Button();
                    tcbut.ID = "But" + tr.Cells[0].Text;
                    tcbut.Text = "Remove";
                    tcbut.Click += new EventHandler(butDynClick);
                    tr.Cells[2].Controls.Clear();
                    tr.Cells[2].Controls.Add(tcbut);
                }

                /*foreach (TableRow ttr in tbl.Rows)
                {
                    if (ttr.Cells[2].Text == "")
                        Session[ttr.Cells[0].Text] = "Sum";
                    else
                        Session[ttr.Cells[0].Text] = ttr.Cells[2].Text;
                }*/

                Session["tbl"] = tbl;
                PnlTable.Controls.Add(tbl);
            }
        }

    }

    private string wrapItAsMessage(string msg, bool isError)
    {
        return "<div class=\"" + (isError == true ? "mbErrorMessage" : "mbMessage") + "\">" + msg + "</div>";
    }

    protected void Page_PreInit(object sender, EventArgs e)
    {
        this.Theme = Session["Theme"].ToString();
        this.Title = "MaxBlox";
    }
    protected void Button1_Click1(object sender, EventArgs e)
    {
        Panel1.Visible = false;
        Panel2.Visible = true;
        Panel3.Visible = false;
    }

    /*public Hashtable loadAggragate()
    {
        String HidString = HidAggragate.Value;
        Hashtable hsh = new Hashtable();

        if (HidString == "") return hsh;

        String[] arrString = HidString.Split(',');
        for (int i = 0; i < arrString.Length; i++)
        {
            String tmp = arrString[i];
            String[] arrtmp = tmp.Split('|');
            if (hsh.ContainsKey(arrtmp[0]))
            {
            }
            else
            {
                hsh.Add(arrtmp[0], arrtmp[1]);
            }
        }
        return hsh;
    }

    public String storeAggragate(Hashtable hsh)
    {
        String hidstr = "";
        foreach (System.Collections.DictionaryEntry de in hsh)
        {
            //System.Collections.DictionaryEntry de = objkey;
            if (hidstr == "")
            {
                hidstr = de.Key + "|" + de.Value;
            }
            else
            {
                hidstr = hidstr + "," + de.Key + "|" + de.Value;
            }
        }
        HidAggragate.Value = hidstr;
        return hidstr;
    }

    public String setAggragate(String item, String agg)
    {
        Hashtable hsh = loadAggragate();
        if (hsh.ContainsKey(item))
        {
            hsh.Remove(item);
        }
        hsh.Add(item, agg);
        return storeAggragate(hsh);
    }*/

    public ArrayList getColumnFromSql(String tableName, String fieldName, String whereField, String whereValue)
    {
        DataTable dt = null;
        HDDatabase hda = new HDDatabase();
        String sql = "select distinct " + fieldName + " from " + tableName + " where " + whereField + "=" + whereValue;
        dt = hda.GetDataTable(sql, "dddd");

        ArrayList arrList = new ArrayList();
        foreach (DataRow row in dt.Rows)
        {
            arrList.Add(row[0]);
        }
        return arrList;
    }

    public ArrayList getColumnBySql(String sql)
    {
        DataTable dt = null;
        HDDatabase hda = new HDDatabase();

        dt = hda.GetDataTable(sql, "dddd");

        ArrayList arrList = new ArrayList();
        foreach (DataRow row in dt.Rows)
        {
            arrList.Add(row[0]);
        }
        return arrList;
    }

    public Object getDataBySql(String sql)
    {
        ArrayList ar = getColumnBySql(sql);
        return ar[0];
    }

    public ArrayList getFieldsBySql(String sql)
    {
        DataTable dt = null;
        HDDatabase hda = new HDDatabase();

        dt = hda.GetDataTable(sql, "dddd");
        ArrayList arrList = new ArrayList();
        foreach (DataColumn colm in dt.Columns)
        {
            arrList.Add(colm.Caption);
        }
        return arrList;
    }

    private void modifyEditMode()
    {
        Session["tbl"] = null;
        //LstColField.Items.Clear();
        //LstRowField.Items.Clear();
        //LstField.Items.Clear();
        String pk = Request.QueryString["PK"];

        ///row processing
        ArrayList ar = getColumnBySql("Select * from sy_analytic_fields where af_row_id='" + Request.QueryString["PK"] + "' and analytic_type='row'");
        for (int i = 0; i < ar.Count; i++)
        {
            String strRow = (String)ar[i];
            strRow = strRow.Trim();

            int count = LstField.Items.Count;
            for (int m = 0; m < count; m++)
            {
                String str = LstField.Items[m].Text;
                str = str.Trim();
                if (str == strRow)
                {
                    LstField.SelectedValue = str;
                    ButRow_Click(null, null);
                    break;
                }
            }
        }

        ///col processing
        ar = getColumnBySql("Select * from sy_analytic_fields where af_row_id='" + Request.QueryString["PK"] + "' and analytic_type='col'");
        for (int i = 0; i < ar.Count; i++)
        {
            String strRow = (String)ar[i];
            strRow = strRow.Trim();

            int count = LstField.Items.Count;
            for (int m = 0; m < count; m++)
            {
                String str = LstField.Items[m].Text;
                str = str.Trim();
                if (str == strRow)
                {
                    LstField.SelectedValue = str;
                    ButCol_Click(null, null);
                    break;
                }
            }
        }

        ///data processing
        //ar = getColumnBySql("Select * from sy_analytic_fields where af_row_id='" + Request.QueryString["PK"] + "' and analytic_type='data'");
        String aggSql = "Select * from sy_analytic_fields where af_row_id='" + Request.QueryString["PK"] + "' and analytic_type='data'";

        DataTable dt = null;
        HDDatabase hda = new HDDatabase();

        dt = hda.GetDataTable(aggSql, "dddd");

        foreach (DataRow row in dt.Rows)
        {
            String strRow = (String)row["analytic_field"];
            String strAgg = null;
            if (row["analytic_agg"] != System.DBNull.Value)
                strAgg = (String)row["analytic_agg"];

            strRow = strRow.Trim();

            int count = LstField.Items.Count;
            for (int m = 0; m < count; m++)
            {
                String str = LstField.Items[m].Text;
                str = str.Trim();
                if (str == strRow)
                {
                    LstField.SelectedValue = str;
                    processDataField(strAgg);
                    //ButData_Click(null, null);
                    break;
                }
            }
        }

        /*for (int i = 0; i < ar.Count; i++)
        {
            String strRow = (String)ar[i];
            strRow = strRow.Trim();

            int count = LstField.Items.Count;
            for (int m = 0; m < count; m++)
            {
                String str = LstField.Items[m].Text;
                str = str.Trim();
                if (str == strRow)
                {
                    LstField.SelectedValue = str;
                    processDataField(str);
                    ButData_Click(null, null);
                    break;
                }
            }
        }*/

    }

    public DataTable getAnalyticFields(String tableName, String whereField, String whereValue)
    {
        DataTable dt = null;
        HDDatabase hda = new HDDatabase();
        String sql = "select * from " + tableName + " where " + whereField + "='" + whereValue + "'";
        dt = hda.GetDataTable(sql, tableName);

        return dt;
    }

    public ArrayList getColumnFromDT(DataTable dt, int colNum)
    {
        ArrayList arrList = new ArrayList();
        foreach (DataRow row in dt.Rows)
        {
            arrList.Add(row[colNum]);
        }
        return arrList;
    }

    public String GetConnectionString(String database)
    {
        return @"provider=microsoft.jet.oledb.4.0;data source=" + System.AppDomain.CurrentDomain.BaseDirectory + database;
    }

    public Object getQuery(String queryid)
    {
        DataTable dt = null;
        HDDatabase hda = new HDDatabase();
        //String sql = "select report_base_sql from analytic_demo.sy_report_sql where report_base_id='" + queryid + "'";
        String sql = "select report_base_sql from sy_report_sql where af_row_id='" + queryid + "'";
        dt = hda.GetDataTable(sql, queryid);
        hda = null;
        return dt.Rows[0][0];
    }

    public Object getData(String tblName, String fldName, String whereField, String whereValue)
    {
        DataTable dt = null;
        HDDatabase hda = new HDDatabase();
        //String sql = "select report_base_sql from analytic_demo.sy_report_sql where report_base_id='" + queryid + "'";
        String sql = "select " + fldName + " from " + tblName + " where " + whereField + "='" + whereValue + "'";
        dt = hda.GetDataTable(sql, tblName);
        hda = null;
        return dt.Rows[0][0];
    }

    public Object GetSchemaItem(String tablename, String fieldname, String retItem)
    {
        //SqlConnection myConnection = new SqlConnection(connectionString);COLUMN_NAME,DATA_TYPE
        string SQL = "SELECT " + retItem + " FROM INFORMATION_SCHEMA.COLUMNS" +
            " WHERE TABLE_NAME='" + tablename + "' and COLUMN_NAME='" + fieldname + "' ORDER BY COLUMN_NAME";
        //SqlDataAdapter schemaDA = new SqlDataAdapter(SQL, myConnection);
        HDDatabase hda = new HDDatabase();
        DataTable schemaTable = hda.GetDataTable(SQL);
        //schemaDA.Fill(ds, tablename);
        //return ds;
        return schemaTable.Rows[0][0];
    }



    public void FillCombo(ListBox listName, ArrayList arrList)
    {
        listName.Items.Clear();
        foreach (Object obj in arrList)
        {
            listName.Items.Add((String)obj);
        }
    }

    public void FillCombo(DropDownList listName, DataTable dt, int textColumn, int valueColumn)
    {
        listName.Items.Clear();

        foreach (DataRow row in dt.Rows)
        {
            ListItem li = new ListItem((String)row[textColumn], (String)row[valueColumn]);
            listName.Items.Add(li);
        }
    }

    public void FillCombo(ListBox listName, DataTable dt, String textColumn, String valueColumn)
    {
        listName.Items.Clear();

        foreach (DataRow row in dt.Rows)
        {
            ListItem li = new ListItem((String)row[textColumn], (String)row[valueColumn]);
            listName.Items.Add(li);
        }
    }

    public void FillCombo(DropDownList listName, ArrayList arrList)
    {
        listName.Items.Clear();
        foreach (Object obj in arrList)
        {
            listName.Items.Add((String)obj);
        }
    }

    public ArrayList getFieldsFromDS(DataSet ds, String tableName)
    {
        ArrayList arrList = new ArrayList();
        foreach (DataColumn colm in ds.Tables[tableName].Columns)
        {
            arrList.Add(colm.Caption);
        }
        return arrList;
    }
    public ArrayList getFieldsFromDT(DataTable dt)
    {
        ArrayList arrList = new ArrayList();
        foreach (DataColumn colm in dt.Columns)
        {
            arrList.Add(colm.Caption);
        }
        return arrList;
    }

    public Hashtable getFieldDetails(DataTable dt, String reqDetail)
    {
        Hashtable hsh = new Hashtable();
        if (reqDetail == "datatype")
        {
            foreach (DataColumn colm in dt.Columns)
            {
                hsh.Add((String)colm.Caption, (String)colm.DataType.Name);
            }
        }
        return hsh;
    }

    private String getFieldDataType(String fldname)
    {
        Hashtable hsh = (Hashtable)Session["FieldDataType"];
        String colName = null;
        String datatype = null;
        foreach (System.Collections.DictionaryEntry kky in hsh)
        {
            colName = (String)kky.Key;
            datatype = (String)kky.Value;
            if (fldname == colName)
                return datatype;
        }
        return null;
    }

    protected void ButQueryList_Click(object sender, EventArgs e)
    {
        if (ListQuery.SelectedIndex >= 0)
        {
            basepath = System.AppDomain.CurrentDomain.BaseDirectory;

            // create a connection to a database
            //connString = GetConnectionString(database);

            Object objQry = getQuery(ListQuery.SelectedItem.Value);
            String strQry = (String)objQry;

            HDDatabase hda = new HDDatabase();
            DataTable dat = hda.GetDataTable(strQry, "myquery");
            hda = null;

            arrList = getFieldsFromDT(dat);
            Hashtable hsh = getFieldDetails(dat, "datatype");

            Session["FieldDataType"] = hsh;
            arrList.Sort();
            FillCombo(LstField, arrList);
            //DataTable dt = GetFields("", "");
            Panel1.Visible = false;
            Panel2.Visible = true;
            Panel3.Visible = false;

            if (isEditMode())
            {
                modifyEditMode();
            }

        }
    }
    protected void ButRow_Click(object sender, EventArgs e)
    {
        LstRowField.SelectedIndex = -1;
        if (LstField.SelectedIndex >= 0)
        {
            LstRowField.Items.Add(LstField.SelectedItem);
            LstField.Items.RemoveAt(LstField.SelectedIndex);
        }
    }
    protected void ButCol_Click(object sender, EventArgs e)
    {
        if (LstField.SelectedIndex >= 0)
        {
            LstColField.SelectedIndex = -1;
            LstColField.Items.Add(LstField.SelectedItem);
            LstField.Items.RemoveAt(LstField.SelectedIndex);
        }
    }


    protected void butDynClick(object sender, EventArgs e)
    {
        Button but = (Button)sender;
        Table tbl = (Table)Session["tbl"];
        Table newtbl = new Table();
        TableRow trToRemove = null;
        foreach (TableRow tr in tbl.Rows)
        {
            if (("But" + tr.Cells[0].Text) == but.ID)
            {
                LstField.Items.Add(tr.Cells[0].Text);
                trToRemove = tr;
            }
            else
            {

                //newtbl.Rows.Add(tr);
            }
        }
        tbl.Rows.Remove(trToRemove);
        //txtTemp.Text = but.Text;
        if (tbl.Rows.Count == 0)
        {
            Session["tbl"] = null;
            PnlTable.Controls.Clear();
        }
        else
        {
            Session["tbl"] = tbl;
            PnlTable.Controls.Clear();
            PnlTable.Controls.Add(tbl);
        }

    }
    private TableRow getDropDownListForData()
    {
        return getDropDownListForData(null);
    }
    private TableRow getDropDownListForData(String selected)
    {
        TableCell tc = new TableCell();
        tc.Text = LstField.SelectedItem.Text;

        DropDownList lst = new DropDownList();
        lst.ID = LstField.SelectedItem.Text;

        lst.Items.Add("- None -");
        //Object obj = GetSchemaItem("ap_emp", "af_row_id", "DATA_TYPE");
        //if (getFieldDataType(LstField.SelectedItem.Text)=="Int32")
        //{
        lst.Items.Add("Sum");
        lst.Items.Add("Avg");
        lst.Items.Add("Count");
        lst.Items.Add("Min");
        lst.Items.Add("Max");
        lst.Items.Add("StDev");
        lst.Items.Add("StDevp");
        lst.Items.Add("Var");
        lst.Items.Add("Varp");

        if (selected != null) lst.SelectedValue = selected;

        TableCell tc2 = new TableCell();
        tc2.Controls.Add(lst);

        TableRow tr = new TableRow();
        tr.Cells.Add(tc);
        tr.Cells.Add(tc2);
        TableCell tc3 = new TableCell();
        //tc3.Text = "";
        Button tcbut = new Button();
        tcbut.ID = "But" + LstField.SelectedItem.Text;
        tcbut.Text = "Remove";
        tcbut.Click += new EventHandler(butDynClick);
        tc3.Controls.Add(tcbut);
        tr.Cells.Add(tc3);

        return tr;
    }

    private void processDataField()
    {
        processDataField(null);
    }

    private void processDataField(String selected)
    {
        if (LstField.SelectedIndex >= 0)
        {

            Table tbl = new Table();
            if (isKeyExistInSession("tbl") && Session["tbl"] != null)
            {
                tbl = (Table)Session["tbl"];
            }
            /*TableCell tc = new TableCell();
            tc.Text = LstField.SelectedItem.Text;*/
            TableRow tr = null;
            if (selected == null)
            {
                tr = getDropDownListForData();
            }
            else
                tr = getDropDownListForData(selected);
            /*DropDownList lst = new DropDownList();
            lst.ID = LstField.SelectedItem.Text;

            lst.Items.Add("- None -");
            //Object obj = GetSchemaItem("ap_emp", "af_row_id", "DATA_TYPE");
            //if (getFieldDataType(LstField.SelectedItem.Text)=="Int32")
            //{
            lst.Items.Add("Sum");
            lst.Items.Add("Avg");
            lst.Items.Add("Count");
            lst.Items.Add("Min");
            lst.Items.Add("Max");
            lst.Items.Add("StDev");
            lst.Items.Add("StDevp");
            lst.Items.Add("Var");
            lst.Items.Add("Varp");*/
            //}

            /*
            TableCell tc2 = new TableCell();
            tc2.Controls.Add(lst);

            TableRow tr = new TableRow();
            tr.Cells.Add(tc);
            tr.Cells.Add(tc2);
            TableCell tc3 = new TableCell();
            //tc3.Text = "";
            Button tcbut = new Button();
            tcbut.ID = "But" + LstField.SelectedItem.Text;
            tcbut.Text = "Remove";
            tcbut.Click += new EventHandler(butDynClick);
            tc3.Controls.Add(tcbut);
            tr.Cells.Add(tc3);*/

            tbl.Rows.Add(tr);

            Session["tbl"] = tbl;
            PnlTable.Controls.Clear();
            PnlTable.Controls.Add(tbl);

            LstField.Items.RemoveAt(LstField.SelectedIndex);

            //LstDataField.SelectedIndex = -1;
            //LstDataField.Items.Add(LstField.SelectedItem);
            //LstField.Items.RemoveAt(LstField.SelectedIndex);
        }
    }

    protected void ButData_Click(object sender, EventArgs e)
    {
        processDataField();
    }

    public int getUniqueNum()
    {
        return 8888;
    }

    public String getCubFileName()
    {
        return "preview";
    }

    private void addToCube(DataSet ds, ArrayList arrDataToStore)
    {
        DataTable dt = ds.Tables["myquery"];

        addToCube(dt, arrDataToStore);
        ds.Dispose();
    }

    private void addToCube(DataTable dt, ArrayList arrDataToStore)
    {
        //DataTable dt = ds.Tables["myquery"];

        foreach (DataRow rec in dt.Rows)
        {
            int len = arrDataToStore.Count;
            object[] cubeData = new object[len];
            int i = 0;
            foreach (Object obj in arrDataToStore)
            {
                cubeData[i++] = rec[(String)obj];
            }
            /*foreach (object col in rec.ItemArray)
            {
                cubeData[i++] = col.ToString();
            }*/
            object cubeDataObject = (object)cubeData;
            axDCube2.AddRowEx(cubeDataObject);
        }
        dt.Dispose();

    }

    private Boolean processQuery()
    {
        String pkid = null;
        foreach (String str in Request.QueryString)
        {
            if (str == "pkid")
            {
                pkid = Request[str];
            }
        }
        if (pkid != null)
        {
            processQuery(pkid);
            return true;
        }
        else
        {
            return false;
        }
    }
    private void processQuery(String pkid)
    {
        DataTable tbl = getAnalyticFields("sy_analytic_fields", "af_row_id", "'" + pkid + "'");

        //Table tbl = (Table)Session["tbl"];

        /* foreach (TableRow ttr in tbl.Rows)
         {
             if (ttr.Cells[2].Text == "")
                 Session[ttr.Cells[0].Text] = "Sum";
             else
                 Session[ttr.Cells[0].Text] = ttr.Cells[2].Text;
         }*/

        /*       Hashtable hsh = new Hashtable();

               foreach (DataRow tr in tbl.Rows)
               {
                   String data = (String)tr[3];
                   hsh.Add(tr[0], data);
                   //Session[tr.Cells[0].Text] = data;
               }

               Session["tbl"] = tbl;

               String sss = LstRowField.Items[0].Value;

               //basepath = System.AppDomain.CurrentDomain.BaseDirectory;

               // create a connection to a database
               //connString = GetConnectionString(database);

               Object objQry = getQuery(ListQuery.SelectedItem.Text);
               String strQry = (String)objQry;
               HDDatabase hds = new HDDatabase();
               DataTable dtt = hds.GetDataTable(strQry, "myquery");
               //dds = getDS("myquery", strQry);
               */
        axDCube2.AutoDataRefresh = false;
        axDCube2.DCConnectType = DynamiCubeLib.DCConnectTypes.DCCT_UNBOUND;
        DynamiCubeLib.Field field1 = null;

        ArrayList arrDataToStore = new ArrayList();
        String tmp;
        foreach (DataRow tr in tbl.Rows)
        {
            tmp = (String)tr[1];
            tmp = tmp.Trim();
            if (tmp == "row")
            {
                field1 = axDCube2.Fields.Add((String)tr[1], (String)tr[1], DynamiCubeLib.OrientationConstants.DCRow);
                arrDataToStore.Add(tmp);
            }
        }

        foreach (DataRow tr in tbl.Rows)
        {
            tmp = (String)tr[1];
            tmp = tmp.Trim();
            if (tmp == "col")
            {
                field1 = axDCube2.Fields.Add((String)tr[1], (String)tr[1], DynamiCubeLib.OrientationConstants.DCColumn);
                arrDataToStore.Add(tmp);
            }
        }

        foreach (DataRow tr in tbl.Rows)
        {
            tmp = (String)tr[1];
            tmp = tmp.Trim();
            if (tmp == "data" && tmp == null)
            {
                field1 = axDCube2.Fields.Add((String)tr[1], (String)tr[1], DynamiCubeLib.OrientationConstants.DCData);
                arrDataToStore.Add(tmp);
            }
            else if ((String)tr[1] == "data" && tr[3] != null)
            {
                field1 = axDCube2.Fields.Add((String)tr[1], (String)tr[1], DynamiCubeLib.OrientationConstants.DCData);
                arrDataToStore.Add(tmp);
                if ((String)tr[3] == "Sum")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCSum;
                else if ((String)tr[3] == "Avg")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCAvg;
                else if ((String)tr[3] == "Count")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCCount;
                else if ((String)tr[3] == "Min")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCMin;
                else if ((String)tr[3] == "Max")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCMax;
                else if ((String)tr[3] == "StDev")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCStDev;
                else if ((String)tr[3] == "StDevp")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCStDevP;
                else if ((String)tr[3] == "Var")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCVar;
                else if ((String)tr[3] == "Varp")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCVarP;
            }
        }

        // refresh the cube (call FetchData event below)
        axDCube2.RefreshData();
        axDCube2.AutoDataRefresh = true;

        Object objQry = getQuery(pkid);
        String strQry = (String)objQry;
        HDDatabase hds = new HDDatabase();
        DataTable dtt = hds.GetDataTable(strQry, "myquery");
        addToCube(dtt, arrDataToStore);

        int uniquenum = getUniqueNum();

        axDCube2.Save(basepath + "cub_files\\" + getCubFileName() + uniquenum + ".cub", DynamiCubeLib.SaveOptions.SOLayoutWithData);
        HidCubName.Value = "cub_files/" + getCubFileName() + uniquenum + ".cub";

        Panel1.Visible = false;
        Panel2.Visible = false;
        Panel3.Visible = true;
    }

    protected void Button4_Click(object sender, EventArgs e)
    {
        Boolean bl = processQuery();
        /*
        if (bl && LstRowField.Items.Count > 0 && LstColField.Items.Count > 0)
        {
         */
        Table tbl = (Table)Session["tbl"];

        /* foreach (TableRow ttr in tbl.Rows)
         {
             if (ttr.Cells[2].Text == "")
                 Session[ttr.Cells[0].Text] = "Sum";
             else
                 Session[ttr.Cells[0].Text] = ttr.Cells[2].Text;
         }*/

        Hashtable hsh = new Hashtable();

        foreach (TableRow tr in tbl.Rows)
        {
            String data = getRequest(tr.Cells[0].Text);
            tr.Cells[2].Text = data;
            hsh.Add(tr.Cells[0].Text, data);
            //Session[tr.Cells[0].Text] = data;
        }

        Session["tbl"] = tbl;

        String sss = LstRowField.Items[0].Value;

        basepath = System.AppDomain.CurrentDomain.BaseDirectory;

        // create a connection to a database
        //connString = GetConnectionString(database);

        Object objQry = getQuery(ListQuery.SelectedItem.Value);
        String strQry = (String)objQry;
        HDDatabase hds = new HDDatabase();
        DataTable dtt = hds.GetDataTable(strQry, "myquery");
        //dds = getDS("myquery", strQry);

        axDCube2.AutoDataRefresh = false;
        axDCube2.DCConnectType = DynamiCubeLib.DCConnectTypes.DCCT_UNBOUND;
        DynamiCubeLib.Field field1 = null;

        ArrayList arrDataToStore = new ArrayList();

        foreach (ListItem lst in LstRowField.Items)
        {
            field1 = axDCube2.Fields.Add(lst.Text, lst.Text, DynamiCubeLib.OrientationConstants.DCRow);
            arrDataToStore.Add(lst.Text);
        }

        foreach (ListItem lst in LstColField.Items)
        {
            field1 = axDCube2.Fields.Add(lst.Text, lst.Text, DynamiCubeLib.OrientationConstants.DCColumn);
            arrDataToStore.Add(lst.Text);
        }

        //foreach (ListItem lst in LstDataField.Items)
        foreach (TableRow rw in tbl.Rows)
        {
            field1 = axDCube2.Fields.Add(rw.Cells[0].Text, rw.Cells[0].Text, DynamiCubeLib.OrientationConstants.DCData);
            if (isKeyExistInHash(hsh, rw.Cells[0].Text))
            {
                arrDataToStore.Add(rw.Cells[0].Text);
                if ((String)hsh[rw.Cells[0].Text] == "Sum")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCSum;
                else if ((String)hsh[rw.Cells[0].Text] == "Avg")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCAvg;
                else if ((String)hsh[rw.Cells[0].Text] == "Count")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCCount;
                else if ((String)hsh[rw.Cells[0].Text] == "Min")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCMin;
                else if ((String)hsh[rw.Cells[0].Text] == "Max")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCMax;
                else if ((String)hsh[rw.Cells[0].Text] == "StDev")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCStDev;
                else if ((String)hsh[rw.Cells[0].Text] == "StDevp")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCStDevP;
                else if ((String)hsh[rw.Cells[0].Text] == "Var")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCVar;
                else if ((String)hsh[rw.Cells[0].Text] == "Varp")
                    field1.AggregateFunc = DynamiCubeLib.AggregateFunctions.DCVarP;
            }
        }
        Session["tblHash"] = hsh;
        // refresh the cube (call FetchData event below)
        axDCube2.RefreshData();
        axDCube2.AutoDataRefresh = true;

        addToCube(dtt, arrDataToStore);

        int uniquenum = getUniqueNum();

        axDCube2.Save(basepath + "cub_files\\" + getCubFileName() + uniquenum + ".cub", DynamiCubeLib.SaveOptions.SOLayoutWithData);
        HidCubName.Value = "cub_files/" + getCubFileName() + uniquenum + ".cub";

        Panel1.Visible = false;
        Panel2.Visible = false;
        Panel3.Visible = true;

        /*
        }
        else if (bl)
        {
        }
        else
        {
            LblMessage.Text = "Some Listbox Empty";
        }
         */
    }
    protected void ButBackToWiz2_Click1(object sender, EventArgs e)
    {
        Panel1.Visible = false;
        Panel2.Visible = true;
        Panel3.Visible = false;
    }
    protected void ButBackToWiz1_Click(object sender, EventArgs e)
    {
        Panel1.Visible = true;
        Panel2.Visible = false;
        Panel3.Visible = false;
    }

    private String getAnalyticsUpdateSql(String pk, String fldname, String fldtype, String id)
    {
        String fld1 = "analytic_field";
        String fld2 = "analytic_type";
        String fld3 = "analytic_id";
        return "update sy_analytic_fields set " + fld1 + "='" + fldname + "'," + fld2 + "='" + fldtype + "'," + fld3 + "='" + id + "' where af_row_id='" + pk + "'";
    }

    private String getAnalyticsUpdateSql(String pk, String fldname, String fldtype, String id, String agg)
    {
        if (agg == "- None -")
        {
            return getAnalyticsUpdateSql(pk, fldname, fldtype, id);
        }
        else
        {
            String fld1 = "analytic_field";
            String fld2 = "analytic_type";
            String fld3 = "analytic_id";
            String fld4 = "analytic_agg";
            return "update sy_analytic_fields set " + fld1 + "='" + fldname + "'," + fld2 + "='" + fldtype + "'," + fld3 + "='" + id + "'," + fld4 + "='" + agg + "' where af_row_id='" + pk + "'";
        }
    }


    private String getAnalyticsInsertSql(String pk, String fldname, String fldtype, String id)
    {
        String fld1 = "analytic_field";
        String fld2 = "analytic_type";
        String fld3 = "analytic_id";
        return "insert into sy_analytic_fields (af_row_id," + fld1 + "," + fld2 + "," + fld3 + ") values('" + pk + "','" + fldname + "','" + fldtype + "','" + id + "')";
    }

    private String getAnalyticsInsertSql(String pk, String fldname, String fldtype, String id, String agg)
    {
        if (agg == "- None -")
        {
            return getAnalyticsInsertSql(pk, fldname, fldtype, id);
        }
        else
        {
            String fld1 = "analytic_field";
            String fld2 = "analytic_type";
            String fld3 = "analytic_id";
            String fld4 = "analytic_agg";
            return "insert into sy_analytic_fields (af_row_id," + fld1 + "," + fld2 + "," + fld3 + "," + fld4 + ") values('" + pk + "','" + fldname + "','" + fldtype + "','" + id + "','" + agg + "')";
        }
    }
    private string getHashKey()
    {
        string ret = "";
        ScreenReader sr = new ScreenReader();
        ret = sr.getHashKey();
        sr = null;
        return ret;
    }
    protected void ButFinish_Click(object sender, EventArgs e)
    {
        String tblname = "sy_analytic";
        String fld1 = "Analytic_name";
        String fld1val = TxtAnalytics.Text;
        String fld2 = "Analytic_desc";
        String fld2val = TxtAnalyticsDesc.Text;
        String fld3 = "Analytic_query";
        String fld3val = ListQuery.SelectedItem.Value;
        String IDfld = "Analytic_id";
        String IDvalue = TxtAnalyticsID.Text;
        String fld4 = "af_row_id";

        System.Data.SqlClient.SqlCommand cmd = null;
        HDDatabase hd = new HDDatabase();

        String sql1 = "";
        String rowid = null;
        if (isEditMode())
        {
            rowid = Request["PK"];
            sql1 = "update " + tblname + " set " + IDfld + " = '" + IDvalue + "', " + fld1 + " = '" + fld1val + "', " + fld2 + " = '" + fld2val + "', " + fld3 + " = '" + fld3val + "' where af_row_id = '" + Request["PK"] + "'";
            String sqlDelete = "delete from sy_analytic_fields where af_row_id='" + rowid + "'";
            cmd = new System.Data.SqlClient.SqlCommand(sqlDelete);
            int successDelete = hd.ExecuteCmdObj(cmd);
        }
        else
        {
            rowid = getHashKey();
            sql1 = "insert into " + tblname + " (" + IDfld + "," + fld1 + "," + fld2 + "," + fld3 + "," + fld4 + ") values('" + IDvalue + "','" + fld1val + "','" + fld2val + "','" + fld3val + "','" + rowid + "')";
        }

        cmd = new System.Data.SqlClient.SqlCommand(sql1);
        int success = hd.ExecuteCmdObj(cmd);
        hd = null;
        cmd = null;
        Table tbl = (Table)Session["tbl"];
        foreach (TableRow tr in tbl.Rows)
        {
            //Hashtable hsh = null;
            //if(isKeyExistInSession("tblHash"))
            //hsh = isKeyExistInSession("tblHash");
            String query = null;
            if (isKeyExistInSession("tblHash") && Session["tblHash"] != null && isKeyExistInHash((Hashtable)Session["tblHash"], tr.Cells[0].Text))
            {
                Hashtable hsh = (Hashtable)Session["tblHash"];
                if (hsh != null)
                {
                    /*if (isEditMode())
                    {
                        query = getAnalyticsUpdateSql(rowid, tr.Cells[0].Text, "data", IDvalue, (String)hsh[tr.Cells[0].Text]);
                    }
                    else
                    {*/
                    query = getAnalyticsInsertSql(rowid, tr.Cells[0].Text, "data", IDvalue, (String)hsh[tr.Cells[0].Text]);
                    //}
                }
                else
                {
                    /*if (isEditMode())
                    {
                        query = getAnalyticsUpdateSql(rowid, tr.Cells[0].Text, "data", IDvalue);
                    }
                    else
                    {*/
                    query = getAnalyticsInsertSql(rowid, tr.Cells[0].Text, "data", IDvalue);
                    //}
                }
            }
            else
            {
                /*if (isEditMode())
                {
                    query = getAnalyticsUpdateSql(rowid, tr.Cells[0].Text, "data", IDvalue);
                }
                else*/
                query = getAnalyticsInsertSql(rowid, tr.Cells[0].Text, "data", IDvalue);
            }
            hd = new HDDatabase();
            cmd = new System.Data.SqlClient.SqlCommand(query);
            hd.ExecuteCmdObj(cmd);
            hd = null;
            cmd = null;
        }

        foreach (ListItem itm in LstRowField.Items)
        {
            String sql = getAnalyticsInsertSql(rowid, itm.Text, "row", IDvalue);
            hd = new HDDatabase();
            cmd = new System.Data.SqlClient.SqlCommand(sql);
            hd.ExecuteCmdObj(cmd);
            hd = null;
            cmd = null;
        }

        foreach (ListItem itm in LstColField.Items)
        {
            String sql = getAnalyticsInsertSql(rowid, itm.Text, "col", IDvalue);
            hd = new HDDatabase();
            cmd = new System.Data.SqlClient.SqlCommand(sql);
            hd.ExecuteCmdObj(cmd);
            hd = null;
            cmd = null;
        }

        //foreach (ListItem itm in LstDataField.Items)
        foreach (TableRow rw in tbl.Rows)
        {
            String sql = getAnalyticsInsertSql(rowid, rw.Cells[0].Text, "data", IDvalue);
            hd = new HDDatabase();
            cmd = new System.Data.SqlClient.SqlCommand(sql);
            hd.ExecuteCmdObj(cmd);
            hd = null;
            cmd = null;
        }


        LstColField.Items.Clear();
        LstRowField.Items.Clear();
        //LstDataField.Items.Clear();
        LstField.Items.Clear();
        Panel1.Visible = true;
        Panel2.Visible = false;
        Panel3.Visible = false;
        Session["tbl"] = null;
    }
    protected void ButRowBack_Click(object sender, EventArgs e)
    {
        LstField.SelectedIndex = -1;
        LstField.Items.Add(LstRowField.SelectedItem);
        LstRowField.Items.RemoveAt(LstRowField.SelectedIndex);
    }
    protected void ButColBack_Click(object sender, EventArgs e)
    {
        LstField.SelectedIndex = -1;
        LstField.Items.Add(LstColField.SelectedItem);
        LstColField.Items.RemoveAt(LstColField.SelectedIndex);
    }
    protected void ButDataBack_Click(object sender, EventArgs e)
    {
        /*LstField.SelectedIndex = -1;
        LstField.Items.Add(LstDataField.SelectedItem);
        LstDataField.Items.RemoveAt(LstDataField.SelectedIndex);*/
    }

    protected void HidAggragate_ValueChanged(object sender, EventArgs e)
    {

    }
    protected void Button6_Click(object sender, EventArgs e)
    {

    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        String str = LstRowField.SelectedValue;
        int ind = LstRowField.SelectedIndex;
        if (ind > 0)
        {
            LstRowField.Items.Remove(str);
            LstRowField.Items.Insert(ind - 1, str);
            LstRowField.SelectedIndex = ind - 1;
        }
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        String str = LstRowField.SelectedValue;
        int ind = LstRowField.SelectedIndex;
        if (ind < (LstRowField.Items.Count - 1))
        {
            LstRowField.Items.Remove(str);
            LstRowField.Items.Insert(ind + 1, str);
            LstRowField.SelectedIndex = ind + 1;
        }
    }
    protected void Button4_Click1(object sender, EventArgs e)
    {
        String str = LstColField.SelectedValue;
        int ind = LstColField.SelectedIndex;
        if (ind > 0)
        {
            LstColField.Items.Remove(str);
            LstColField.Items.Insert(ind - 1, str);
            LstColField.SelectedIndex = ind - 1;
        }
    }
    protected void Button5_Click(object sender, EventArgs e)
    {
        String str = LstColField.SelectedValue;
        int ind = LstColField.SelectedIndex;
        if (ind < (LstColField.Items.Count - 1))
        {
            LstColField.Items.Remove(str);
            LstColField.Items.Insert(ind + 1, str);
            LstColField.SelectedIndex = ind + 1;
        }
    }
    protected void Button6_Click1(object sender, EventArgs e)
    {

    }
}
